package DAO.MySQL;

import java.sql.Connection;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.junit.Test;

import DAO.OrderDAO;
import Model.Order;
import Util.JDBCUtil;

/**
 * @author TONY
 *
 */
public class OrderDAOImpl implements OrderDAO{
	private static Connection conn;

	public Order getOrderByOrderId(int id)
	{
		conn=JDBCUtil.GetConnection();
		String sql="select ordernumber,em.loginname,customerid,orderdate " +
				"from orderinfo oi,employee em where oi.loginname=em.loginname and ordernumber=?";
		PreparedStatement ps;
		ResultSet rs;
		Order or=null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			while(rs.next())
			{
				or=new Order();
				or.setOrdernum(rs.getInt("ordernumber"));
			    or.setCustomerid(rs.getInt("customerid"));
			    or.setEmployeename(rs.getString("loginname"));
				or.setOrderdate(rs.getDate("orderdate"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return or;
		
	}
	
	public void insertNewOrder(Order order)
	{

		conn=JDBCUtil.GetConnection();
		String sql="INSERT INTO `orderinfo` (`ordernumber`, `customerid`, `loginname`, `orderdate`) " +
				"VALUES (?, ?, ?, ?)";
		PreparedStatement ps;
		java.sql.Date date = new java.sql.Date(order.getOrderdate().getTime());

		try
		{
		ps=conn.prepareStatement(sql);
		ps.setInt(1, order.getOrdernum());
		ps.setInt(2, order.getCustomerid());
		ps.setString(3, order.getEmployeename());
		ps.setDate(4,  date);
		ps.execute();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}	
		
	}
	public static List<Order> getAllOrder()
	{
		List<Order> list= new ArrayList<Order>();
		conn=JDBCUtil.GetConnection();
		String sql="select * from orderinfo";
		PreparedStatement ps;
		ResultSet rs;
		Order or=null;
		try {
			ps = conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next())
			{
				or=new Order();
				or.setOrdernum(rs.getInt("ordernumber"));
			    or.setCustomerid(rs.getInt("customerid"));
			    or.setEmployeename(rs.getString("loginname"));
				or.setOrderdate(rs.getDate("orderdate"));
				list.add(or);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
		
	}
	public static List<Order> getOrderBtwDate(Date stardate,Date enddate)
	{
		List<Order> list= new ArrayList<Order>();
		conn=JDBCUtil.GetConnection();
		java.sql.Date stardatedate = new java.sql.Date(stardate.getTime());
		java.sql.Date enddatedate = new java.sql.Date(enddate.getTime());
		String sql="SELECT * FROM `orderinfo` WHERE orderdate between ? and ?";
		PreparedStatement ps;
		try
		{
		ps=conn.prepareStatement(sql);
		ps.setDate(1, stardatedate);
		ps.setDate(2,enddatedate);
		ResultSet rs;
		rs=ps.executeQuery();
		Order or=null;
		while(rs.next())
		{
			or=new Order();
			or.setOrdernum(rs.getInt("ordernumber"));
		    or.setCustomerid(rs.getInt("customerid"));
		    or.setEmployeename(rs.getString("loginname"));
			or.setOrderdate(rs.getDate("orderdate"));
			list.add(or);
		}
		}
		catch (Exception e) {
			// TODO: handle exception
		}
		return list;
	}

}
